package com.css.bjdt_report.executive.util;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.css.bjdt_report.cell.entity.Cell;
import com.css.bjdt_report.dataSetManage.entity.ViewDataItem;
import com.css.bjdt_report.report.entity.ViewRptStatCategory;
import com.css.bjdt_report.report.entity.ViewRptTemplateCondition;
import com.css.bjdt_report.report.entity.RptTemplateCondition.ConditionType;
import com.css.bjdt_report.util.ReportConstant;

public class SqlExpProducer {
	
	private static SqlExpProducer instance;
	
	private SqlExpProducer(){
	}
	
	public static synchronized SqlExpProducer getInstance(){
		if(instance == null){
			instance = new SqlExpProducer();
		}
		return instance;
	}	
	//以条件类型为key
	private Map<String,List<ViewRptTemplateCondition>> conditionMap;
	//以信息集id+主外键标识符为key
	private Map<String,ViewDataItem> itemMap;
	/**
	 * 生成sql表达式
	 * @param cell
	 */
	public void produceSqlExp(Cell cell){
		if(!verifyCondition(cell)){
			return;
		}		
		String baseClause = ReportConstant.BASE_SQL;
		baseClause = baseClause.replace(ReportConstant.MASTER_TABLE, cell.getStatCategory().getSetName());
		appendTotalCondition(cell);
		appendRowCondition(cell);
		appendColCondition(cell);
		appendCellCondition(cell);
		appendTimeCondition(cell);
		appendUnitCondition(cell);
		String sqlExp = baseClause + cell.getJoinClause() + ReportConstant.WHERE_KEY + cell.getWhereClause();
		cell.setSqlExp(sqlExp);
	}
	/**
	 * 验证单元格是否至少参与一种条件(除总控条件以外)
	 * @param cell
	 * @return
	 */
	private boolean verifyCondition(Cell cell) {
		List<ViewRptTemplateCondition> rowConditions = conditionMap.get(ConditionType.row.getKey());
		for (ViewRptTemplateCondition rowCondition : rowConditions) {
			if(cell.getRelRowNum() != null){
				if(cell.getRelRowNum().toString().equals(rowCondition.getRelRowNum())){
					return true;
				}
			}
		}
		List<ViewRptTemplateCondition> colConditions = conditionMap.get(ConditionType.col.getKey());
		for (ViewRptTemplateCondition colCondition : colConditions) {
			if(cell.getRelColNum() != null){
				if(cell.getRelColNum().toString().equals(colCondition.getRelColNum())){
					return true;
				}
			}
		}
		List<ViewRptTemplateCondition> cellConditions = conditionMap.get(ConditionType.cell.getKey());
		for (ViewRptTemplateCondition cellCondition : cellConditions) {
			if(cell.getAbsColNum().toString().equals(cellCondition.getAbsColNum())
					&& cell.getAbsRowNum().toString().equals(cellCondition.getAbsRowNum())){
				return true;
			}
		}
		return false;
	}

	/**
	 * 添加条件通用方法
	 * @param cell
	 * @param conditions
	 */
	public void appendCondition(Cell cell, List<ViewRptTemplateCondition> conditions){
		StringBuilder joinClause = new StringBuilder();
		StringBuilder whereClause = new StringBuilder();
		ViewRptStatCategory statCategory = cell.getStatCategory();
		for (ViewRptTemplateCondition condition : conditions) {
			//如果条件中的信息集不等于统计类别中的信息集,那么是多表联查,否则是单表查询
			if(!statCategory.getSetId().equals(condition.getSetId())){
				String joinSql = ReportConstant.JOIN_SQL;
				joinSql = joinSql.replace(ReportConstant.MASTER_TABLE, statCategory.getSetName())
				.replace(ReportConstant.MASTER_TABLE_COLUMN, itemMap.get(statCategory.getSetId()+ReportConstant.FK).getItemName())
				.replace(ReportConstant.SUB_TABLE, condition.getSetName())
				.replace(ReportConstant.SUB_TABLE_COLUMN, itemMap.get(condition.getSetId()+ReportConstant.PK).getItemName());
				//多表联查时去掉重复join语句
				if(!joinClause.toString().contains(joinSql)){
					joinClause.append(joinSql);					
				}
			}
			//如果是1那么在字段前加（,如果是2那么在条件值后,关系运算符前加）
			String left = "1".equals(condition.getComboType()) ? "(" : "";
			String right= "2".equals(condition.getComboType()) ? ")" : "";
			
			String whereSql = ReportConstant.WHERE_SQL;
			whereSql = whereSql.replace(ReportConstant.LEFT, left)
			.replace(ReportConstant.MASTER_TABLE, condition.getSetName())
			.replace(ReportConstant.MASTER_TABLE_COLUMN, condition.getItemName())
			.replace(ReportConstant.RELA_OPERATOR, condition.getRelaOperatorName())			
			.replace(ReportConstant.RIGHT, right)
			.replace(ReportConstant.LOGIC_OPERATOR, condition.getLogicOperatorName());
			if(condition.getRealConditionVal() != null){
				whereSql = whereSql.replace(ReportConstant.CONDITION_VAL, condition.getRealConditionVal());
			}
			whereClause.append(whereSql);
		}
		//如果where子句与join子句均为空,那么直接赋值,否则拼加到从句最后面
		if(StringUtils.isBlank(cell.getWhereClause()) && StringUtils.isBlank(cell.getJoinClause())){
			cell.setWhereClause(whereClause.toString());
			cell.setJoinClause(joinClause.toString());				
		}else{
			cell.setWhereClause(cell.getWhereClause()+ReportConstant.AND_KEY+whereClause.toString());
			cell.setJoinClause(cell.getJoinClause()+joinClause.toString());	
		}
	}
	
	/**
	 * 添加统计时间条件
	 * @param cell
	 */
	private void appendTimeCondition(Cell cell){
		ViewRptStatCategory statCategory = cell.getStatCategory();
		if(StringUtils.isNotBlank(statCategory.getItemStatTimeName())){
			ViewRptTemplateCondition startTimeCnd = new ViewRptTemplateCondition();
			startTimeCnd.setSetId(statCategory.getSetId());
			startTimeCnd.setSetName(statCategory.getSetName());
			startTimeCnd.setItemName(statCategory.getItemStatTimeName());
			startTimeCnd.setRelaOperator("2");
			startTimeCnd.setConditionVal(ReportConstant.START_TIME_CONDITION_VAL);
			startTimeCnd.setValType(ReportConstant.PROTOTYPE);
			startTimeCnd.setLogicOperator("1");
			ViewRptTemplateCondition endTimeCnd = new ViewRptTemplateCondition();
			endTimeCnd.setSetId(statCategory.getSetId());
			endTimeCnd.setSetName(statCategory.getSetName());
			endTimeCnd.setItemName(statCategory.getItemStatTimeName());
			endTimeCnd.setRelaOperator("3");
			endTimeCnd.setConditionVal(ReportConstant.END_TIME_CONDITION_VAL);
			endTimeCnd.setValType(ReportConstant.PROTOTYPE);
			List<ViewRptTemplateCondition> timeConditions = new ArrayList<ViewRptTemplateCondition>();
			timeConditions.add(startTimeCnd);
			timeConditions.add(endTimeCnd);
			appendCondition(cell, timeConditions);			
		}
	}
	
	/**
	 * 添加人员所在党组织条件
	 * @param cell
	 */
	private void appendUnitCondition(Cell cell){
		ViewRptTemplateCondition unitCnd = new ViewRptTemplateCondition();
		ViewRptStatCategory statCategory = cell.getStatCategory();
		unitCnd.setSetId(statCategory.getSetId());
		unitCnd.setSetName(statCategory.getSetName());
		unitCnd.setItemName(statCategory.getItemUnitName());
		unitCnd.setRelaOperator("1");
		unitCnd.setConditionVal(ReportConstant.UNIT_CONDITION_VAL);
		unitCnd.setValType(ReportConstant.PROTOTYPE);
		List<ViewRptTemplateCondition> unitConditions = new ArrayList<ViewRptTemplateCondition>();
		unitConditions.add(unitCnd);
		appendCondition(cell, unitConditions);
	}
	
	/**
	 * 添加总控条件
	 * @param cell
	 */
	private void appendTotalCondition(Cell cell){
		List<ViewRptTemplateCondition> totalConditions = conditionMap.get(ConditionType.total.getKey());
		if(totalConditions != null)
			appendCondition(cell, totalConditions);
	}
	
	/**
	 * 添加行条件
	 * @param cell
	 */
	private void appendRowCondition(Cell cell){
		List<ViewRptTemplateCondition> rowConditions = handleRowCondition(conditionMap.get(ConditionType.row.getKey()), cell);
		if(!rowConditions.isEmpty()){
			appendCondition(cell, rowConditions);
		}
	}

	/**
	 * 添加列条件
	 * @param cell
	 */
	private void appendColCondition(Cell cell){
		List<ViewRptTemplateCondition> colConditions = handleColCondition(conditionMap.get(ConditionType.col.getKey()), cell);
		if(!colConditions.isEmpty()){
			appendCondition(cell, colConditions);
		}
	}
	
	/**
	 * 添加单元格条件
	 * @param cell
	 */
	private void appendCellCondition(Cell cell){
		List<ViewRptTemplateCondition> cellConditions = handleCellCondition(conditionMap.get(ConditionType.cell.getKey()), cell);
		if(!cellConditions.isEmpty()){
			appendCondition(cell, cellConditions);
		}
	}
	
	/**
	 * 处理行条件(取出所有匹配单元格行号的条件)
	 * @param conditions
	 * @param cell
	 * @return
	 */
	private List<ViewRptTemplateCondition> handleRowCondition(List<ViewRptTemplateCondition> conditions, Cell cell){
		List<ViewRptTemplateCondition> list = new ArrayList<ViewRptTemplateCondition>();
		if(conditions != null){
			for (ViewRptTemplateCondition condition : conditions) {
				if(cell.getRelRowNum() != null){
					if(cell.getRelRowNum().toString().equals(condition.getRelRowNum())){
						list.add(condition);
					}
				}
			}			
		}
		return list;
	}
	
	/**
	 * 处理列条件(取出所有匹配单元格列号的条件)
	 * @param conditions
	 * @param cell
	 * @return
	 */
	private List<ViewRptTemplateCondition> handleColCondition(List<ViewRptTemplateCondition> conditions, Cell cell){
		List<ViewRptTemplateCondition> list = new ArrayList<ViewRptTemplateCondition>();
		if(conditions != null){
			for (ViewRptTemplateCondition condition : conditions) {
				if(cell.getRelColNum() != null){
					if(cell.getRelColNum().toString().equals(condition.getRelColNum())){
						list.add(condition);
					}
				}
			}			
		}
		return list;
	}
	
	/**
	 * 处理单元格条件(取出所有匹配单元格行号和列号的条件,绝对坐标匹配)
	 * @param conditions
	 * @param cell
	 * @return
	 */
	private List<ViewRptTemplateCondition> handleCellCondition(List<ViewRptTemplateCondition> conditions, Cell cell){
		List<ViewRptTemplateCondition> list = new ArrayList<ViewRptTemplateCondition>();
		if(conditions != null){
			for (ViewRptTemplateCondition condition : conditions) {
				if(cell.getAbsColNum().toString().equals(condition.getAbsColNum())
						&& cell.getAbsRowNum().toString().equals(condition.getAbsRowNum())){
					list.add(condition);
				}
			}			
		}
		return list;
	}

	public void setConditionMap(Map<String, List<ViewRptTemplateCondition>> conditionMap) {
		this.conditionMap = conditionMap;
	}
	
	public void setItemMap(Map<String, ViewDataItem> itemMap) {
		this.itemMap = itemMap;
	}

	public void destory(){
		if(conditionMap != null){
			conditionMap.clear();
			conditionMap = null;
		}
		if(itemMap != null){
			itemMap.clear();
			itemMap = null;
		}
	} 
}
